import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import path from 'path';
import { ResultData } from './result.js';
// 获取当前文件的绝对路径
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 连接到SQLite数据库
const db = new sqlite3.Database(path.join(__dirname, '../database.sqlite'), (err) => {
  if (err) {
    console.error('Error connecting to database:', err.message);
  } else {
    console.log('Connected to the SQLite database.');
  }
});

// 初始化数据库表
db.serialize(() => {
  const createTables = `
    -- Create student table
    CREATE TABLE IF NOT EXISTS Students(
        StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
        Name VARCHAR(20),
        Email VARCHAR(30),
        Password VARCHAR(30),
        Photo VARCHAR(20),
        Bio VARCHAR(255),
        Interests TEXT,
        Courses VARCHAR(255),
        Achievements VARCHAR(255),
        IsDeleted BOOLEAN DEFAULT 0
    );
    
    CREATE TABLE IF NOT EXISTS Tags(
        TagId INTEGER PRIMARY KEY AUTOINCREMENT,
        Type INT, -- 1 for activity , 2 for post
        Name VARCHAR(20),
        IsDeleted BOOLEAN DEFAULT 0
    );
    
    -- Create Noticeboard table
    CREATE TABLE IF NOT EXISTS NoticeboardPosts(
        PostID INTEGER PRIMARY KEY AUTOINCREMENT,
        Title VARCHAR(32),
        Content VARCHAR(2000),
        PostDate DATE,
        StudentID INT,
        TagID INT,
        IsHot BOOLEAN DEFAULT 0,
        IsDeleted BOOLEAN DEFAULT 0,
        FOREIGN KEY (StudentID) REFERENCES Students(StudentID) -- Foreign key reference to student table
    );

    -- Create Comments table
    CREATE TABLE IF NOT EXISTS Comments(
        CommentID INTEGER PRIMARY KEY AUTOINCREMENT,
        PostID INT,
        StudentID INT,
        CommentText VARCHAR(1000),
        CommentDate DATE,
        IsDeleted BOOLEAN DEFAULT 0,
        FOREIGN KEY (PostID) REFERENCES NoticeboardPosts(PostID), -- Foreign key reference to noticboard
        FOREIGN KEY (StudentID) REFERENCES Students(StudentID) -- Foreign key reference to student table
    );

    -- Create Likes table
    CREATE TABLE IF NOT EXISTS Likes(
        LikeID INTEGER PRIMARY KEY AUTOINCREMENT,
        PostID INT,
        StudentID INT,
        LikeDate DATE,
        FOREIGN KEY (PostID) REFERENCES NoticeboardPosts(PostID), -- Foreign key reference to noticboard
        FOREIGN KEY (StudentID) REFERENCES Students(StudentID) -- Foreign key reference to student table
    );

    -- Create Share table
    CREATE TABLE IF NOT EXISTS Share(
        ShareID INTEGER PRIMARY KEY AUTOINCREMENT,
        PostID INT,
        StudentID INT,
        ShareDate DATE,
        FOREIGN KEY (PostID) REFERENCES NoticeboardPosts(PostID), -- Foreign key reference to noticboard
        FOREIGN KEY (StudentID) REFERENCES Students(StudentID) -- Foreign key reference to student table
    );
  `;

  db.exec(createTables, (err) => {
    if (err) {
      console.error('Error creating tables:', err.message);
    } else {
      console.log('Tables created successfully.');
    }
  });
});

// 枚举对象：定义数据库操作类型
export const DB_METHOD = Object.freeze({
  ALL: 'all',
  RUN: 'run',
  GET: 'get',
});

class SqlExecUtil {
  exec = (method, query, params = []) => {
    return new Promise((resolve, reject) => {
      try {
        console.log('Executing SQL:', query, params);
        db[method](query, params, (err, result) => {
          if (err) {
            console.error('Database query error:', err);
            reject(ResultData.error(err));
          } else {
            console.log('Query result:', result);
            resolve(result);
          }
        });
      } catch (err) {
        console.error('Unexpected error in sqlExecUtil:', err);
        reject(ResultData.error(err));
      }
    });
  };
  
  async all(query, params) {
    return await this.exec(DB_METHOD.ALL, query, params);
  }
  
  async getLimit(query, params, page = 1) {
    query += ' LIMIT ? OFFSET ?';
    const limit = 20;
    const offset = (page - 1) * limit;
    const list = await this.exec(DB_METHOD.ALL, query, [...params, limit, offset]);
    return {list, page: parseInt(page), limit};
  }

  async run(query, params) {
    return await this.exec(DB_METHOD.RUN, query, params);
  }

  async get(query, params) {
    return await this.exec(DB_METHOD.GET, query, params);
  }

  async beginTransaction() {
    return new Promise((resolve, reject) => {
      db.run('BEGIN TRANSACTION', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  async commit() {
    return new Promise((resolve, reject) => {
      db.run('COMMIT', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  async rollback() {
    return new Promise((resolve, reject) => {
      db.run('ROLLBACK', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }
}

export const sqlUtil = new SqlExecUtil();
export default db;

export const withTransaction = async (callback) => {
    try {
        await sqlUtil.beginTransaction();
        const result = await callback();
        await sqlUtil.commit();
        return result;
    } catch (error) {
        await sqlUtil.rollback();
        throw error;
    }
};